

/****** Object:  StoredProcedure [dbo].[get_QA_Dashboard]    Script Date: 5/29/2021 4:09:44 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--GET_QA_DASHBOARD 'dev'
CREATE PROC [dbo].[get_QA_Dashboard]
@AGENT_NAME VARCHAR(100)
AS BEGIN
DECLARE @SQLQUERY VARCHAR(MAX),@URL VARCHAR(300)
SELECT @URL=WAVE_ADDRESS FROM COMPANY_SETUP
SET  @SQLQUERY='SELECT TOP 10 Q_id, AGENT_NAME,AUDITOR_NAME,CUSTOMER_NAME,DATE_OF_AUDIT,DATE_OF_CALL,EXTERNAL_PERFORMANCE,INTERNAL_PERFORMANCE,FATAL_ERROR_CALL,
													  CALL_SUMMARY,AREAS_OF_STRENGTH,AREAS_OF_DEVELOPMENT,'''+@URL+'''+RecordingUrl [RecordingUrl] FROM QA_AGENTDETAILS WHERE AGENT_NAME='''+@AGENT_NAME+''' and isnull(FeedbackDone,'''')=''''  ORDER BY DATE_OF_AUDIT DESC ;'

	EXEC(@SQLQUERY)
	print(@SQLQUERY)
	END

GO

/****** Object:  StoredProcedure [dbo].[get_QA_Dashboard_Month]    Script Date: 5/29/2021 4:09:44 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




--[get_QA_Dashboard_week] 'Dev'
CREATE Proc [dbo].[get_QA_Dashboard_Month]
@Agent_name varchar(100)
As begin
declare @Sqlquery varchar(max)
set @Sqlquery='select  Count(*)NoAuditer,avg(Internal_Performance)AVGSCORE ,Sum(case when Fatal_Error_Call=1  then 1 else 0  end )FatalErrorCount from QA_AGENTDETAILS 
where datepart(Month,Date_of_Audit) =datepart(MONTH,getdate()) and datepart(YEAR,Date_of_Audit) =datepart(YEAR,getdate()) and  Agent_Name ='''+@Agent_name+''' group by Agent_Name' 

exec(@Sqlquery)
end

GO

/****** Object:  StoredProcedure [dbo].[get_QA_Dashboard_week]    Script Date: 5/29/2021 4:09:44 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--[get_QA_Dashboard_week] 'dev'
CREATE Proc [dbo].[get_QA_Dashboard_week]
@Agent_name varchar(100)
As begin
declare @Sqlquery varchar(max)
set @Sqlquery='select case when DAY(Date_of_Audit) between 1 and 7 then ''Week 1''
when DAY(Date_of_Audit) between 8 and 16 then ''Week 2''
when DAY(Date_of_Audit) between 16 and 23 then ''Week 3''
else ''Week 4'' end [Week], Count(*)NoAuditer,avg(Internal_Performance)AVGSCORE ,Sum(case when Fatal_Error_Call=1  then 1 else 0  end )FatalErrorCount from QA_AGENTDETAILS 
where datepart(Month,Date_of_Audit) =datepart(Month,getdate()) and datepart(YEAR,Date_of_Audit) =datepart(YEAR,getdate()) and  Agent_Name ='''+@Agent_name+''' group by Agent_Name , case when DAY(Date_of_Audit) between 1 and 7 then ''Week 1''
when DAY(Date_of_Audit) between 8 and 16 then ''Week 2''
when DAY(Date_of_Audit) between 16 and 23 then ''Week 3''
else ''Week 4'' end' 

exec(@Sqlquery)
print(@Sqlquery)
end

GO

/****** Object:  StoredProcedure [dbo].[get_QALeadDetails]    Script Date: 5/29/2021 4:09:44 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE proc [dbo].[get_QALeadDetails] @pid int,@from varchar(19),@to varchar(19)=NULL,@IsCount bit=0  as begin

declare @SqlQuery varchar(max),@CallStatus varchar(100);
select top 1 @CallStatus=CallStatus from QA_Page_Master1 where Process_id=@pid
If isnull(@CallStatus,'')!=''
begin	
		if @IsCount=0 begin
		set @SqlQuery='SELECT Convert(date,Start_Time)Date,Count(1)QALeadCount
					   FROM PROCESS_'+Convert(varchar,@Pid)+'_log 
					   WHERE CallStatus='''+@CallStatus+''' and Start_time>='''+@from+''' '+ Case when isnull(@to,' 00:00')!=' 00:00' then   ' and Start_time<='''+@to+'''' else '' end +'
			           GROUP BY CONVERT(date,Start_Time) ';
		end
		else 
		begin
		set @SqlQuery='SELECT Log_id,Start_Time,End_time,Agent_name 
					   FROM PROCESS_'+Convert(varchar,@Pid)+'_log 
					   WHERE CallStatus='''+@CallStatus+''' and Start_time>='''+@from+''' '+ Case when isnull(@to,' 00:00')!=' 00:00' then   ' and Start_time<='''+@to+'''' else '' end ;

		end
	end
	else 
	begin
	set @SqlQuery='Select ''Disposition Not Found Kindly Check QA Master Setting''ErrorMsg ';
	end
	exec (@SqlQuery)
end
GO

/****** Object:  StoredProcedure [dbo].[Get_QalityApproveData]    Script Date: 5/29/2021 4:09:44 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



create Proc [dbo].[Get_QalityApproveData] 

@Pid varchar(10)

As begin



Declare @Sqlquery varchar(max)


set @Sqlquery='	select Reff_No[Ref ID],Application_No[APP],Campaign_Ids[CAMPAIGN_ID],TITLE,FORENAME,SURNAME,SEX,Process_'+@Pid+'_Master.Phone_No [MOBILE_TEL],Convert(varchar(10),End_Time,121)[CALL DATE],
					   PACCHOLD,Plan_Value[Plan],CallStatus[Dispostion],Agent_Name[Agent Name],EMI_OFFERED[EMI OFFERED],Status,Getdate()[Sent For Capture],EMI_Tenure[Tenure(In)],''''[Final Status],Remarks[Remark],log_id

				from Process_'+@Pid+'_Master 

				join Process_'+@Pid+'_Log on Process_'+@Pid+'_log.master_id=Process_'+@Pid+'_master.master_id

				

				where Qa_Disposition=''Quality Approve'' and isnull(FLag,0)=0  '

exec(@Sqlquery)



end 

GO

/****** Object:  StoredProcedure [dbo].[Get_QalityApproveDataAllRecording]    Script Date: 5/29/2021 4:09:44 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



create Proc [dbo].[Get_QalityApproveDataAllRecording] 

@Pid varchar(10)

As begin



Declare @Sqlquery varchar(max)

set @Sqlquery='	select Process_'+@Pid+'_SubLog.Crm_unique_Id,Process_'+@Pid+'_SubLog.Unique_Id,Process_'+@Pid+'_SubLog.Rec_file 

				from Process_'+@Pid+'_log 

				join (select Master_id from Process_'+@Pid+'_log where Qa_Disposition=''Quality Approve'')QAlog on Process_'+@Pid+'_log.Master_id=QAlog.Master_Id

				join Process_'+@Pid+'_SubLog on Process_'+@Pid+'_log.Crm_unique_Id=Process_'+@Pid+'_SubLog.crm_unique_id

				where isnull(Process_'+@Pid+'_SubLog.Flag,0)=0  '

exec(@Sqlquery)







end 

GO




/****** Object:  StoredProcedure [dbo].[QA_AGENT_WISE_Report]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--Exec QA_AGENT_WISE_report '0','0','0','20200101','20200401','AgentWise','1','1'
CREATE proc [dbo].[QA_AGENT_WISE_Report]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS
BEGIN

	SET NOCOUNT ON;
	
	DECLARE @SQL VARCHAR(MAX)
	
	set @SQL='IF  OBJECT_ID(N''tempdb..#temp'') IS NOT NULL
DROP TABLE #temp
select ROW_NUMBER() OVER (Order by QAA.Agent_Name)SrNo,
	QAA.Agent_Name,
	Emp_Code,
	Avg(External_Performance)[CallScore],
	CASE WHEN Avg(External_Performance)>=91 THEN ''A'' WHEN Avg(External_Performance)>=86 
	and Avg(External_Performance)<=90 THEN ''B'' ELSE ''C'' END [Category] 
	INTO #temp
	from QA_AgentDetails QAA join Admin_Agent_Master AAM ON QAA.Agent_Name=AAM.Agent_Name where 1=1'
		IF(@tlid!='0')
		SET @sql+=' AND TLname='''+@tlid+''' ';
		IF(@agentid!='0')
		SET @sql+=' AND QAA.Agent_Name='''+@agentid+''' ';
		IF(@qaid!='0')
		SET @sql+=' AND Auditor_Name='''+@qaid+''' ';
		SET @sql+=CASE WHEN @from='' THEN ' AND CONVERT(DATE,Date_of_Audit)>=CONVERT(DATE,Getdate()) ' ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END;
		SET @sql+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END;
		set @SQL+='group by QAA.Agent_Name,Emp_Code;

  SELECT SrNo,Agent_Name,Emp_Code,CallScore from #temp
	
	DECLARE @TotalAgent INT=0
	SELECT @TotalAgent=COUNT(1) FROM #temp;
	
	SELECT ''1''SrNo,''A (Good)'' Call_Quality_Category,isnull(SUM(CASE WHEN CallScore>=91 THEN 1 ELSE 0 END),0) Number_of_Agents,
	isnull(Round((Convert(decimal(10,2),SUM(CASE WHEN CallScore>=91 THEN 1 ELSE 0 END)*100))/@TotalAgent,2),0) per_Contribution
	FROM #temp where CallScore>=95
	UNION ALL
	SELECT ''2'',''B (Average)'' Call_Quality_Category,isnull(SUM(CASE WHEN CallScore>=86 and CallScore<=90 THEN 1 ELSE 0 END),0) ,
	isnull(Round((Convert(decimal(10,2),SUM(CASE WHEN CallScore>=86 and CallScore<=90 THEN 1 ELSE 0 END)*100))/@TotalAgent,2),0) per_Contribution
	FROM #temp where CallScore>=91 and CallScore<=94
	UNION ALL
	SELECT ''3'',''C (Moderate)'' Call_Quality_Category,isnull(SUM(CASE WHEN CallScore<86 THEN 1 ELSE 0 END),0) ,
	isnull(Round((Convert(decimal(10,2),SUM(CASE WHEN CallScore<86 THEN 1 ELSE 0 END)*100))/@TotalAgent,2),0) per_Contribution
	FROM #temp where CallScore<90;'
	
	EXEC(@SQL)
	PRINT(@SQL)

END
GO

/****** Object:  StoredProcedure [dbo].[QA_AGENT_WISE_Report_WeekWise]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




--Exec QA_AGENT_WISE_Report_WeekWise '0','0','0','2','AgentWise','1','1'
CREATE proc [dbo].[QA_AGENT_WISE_Report_WeekWise]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @month VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS
BEGIN

	SET NOCOUNT ON;
	
	DECLARE @SQL VARCHAR(MAX)
	
	set @SQL='IF  OBJECT_ID(N''tempdb..#temp'') IS NOT NULL
DROP TABLE #temp
select ROW_NUMBER() OVER (Order by QAA.Agent_Name)SrNo,
	QAA.Agent_Name,
	Emp_Code,
	Avg(External_Performance)[CallScore],
	CASE WHEN Avg(External_Performance)>=91 THEN ''A'' WHEN Avg(External_Performance)>=86 
	and Avg(External_Performance)<=90 THEN ''B'' ELSE ''C'' END [Category] ,(CASE WHEN day(Date_of_Audit) between 1 and 7 THEN ''week1'' 
WHEN day(Date_of_Audit) between 8 and 15 THEN ''week2'' 
WHEN day(Date_of_Audit) between 15 and 23 THEN ''week3''
WHEN day(Date_of_Audit)>= 24 THEN ''week4'' end) Weeks
	INTO #temp
	from QA_AgentDetails QAA join Admin_Agent_Master AAM ON QAA.Agent_Name=AAM.Agent_Name where 1=1'
		IF(@tlid!='0')
		SET @sql+=' AND TLname='''+@tlid+''' ';
		IF(@agentid!='0')
		SET @sql+=' AND QAA.Agent_Name='''+@agentid+''' ';
		IF(@qaid!='0')
		SET @sql+=' AND Auditor_Name='''+@qaid+''' ';
		SET @sql+=CASE WHEN @month='' THEN ' and year(Date_of_Audit)= year(getdate()) and month(Date_of_Audit)>=month(getdate())' ELSE 
		'and year(Date_of_Audit)= year(getdate()) and month(Date_of_Audit)='''+@month+''' ' END;
		set @SQL+='group by QAA.Agent_Name,Emp_Code,day(Date_of_Audit);
		
	SELECT *
FROM
(
  SELECT SrNo,Agent_Name,Emp_Code,CallScore,weeks
  FROM #temp
) ss
PIVOT 
(
  sum(CallScore) FOR
  weeks IN (week1,week2,week3,week4)
) AS ff
	
	DECLARE @TotalAgent INT=0
	SELECT @TotalAgent=COUNT(1) FROM #temp;
	
	SELECT ''1''SrNo,''A (Good)'' Call_Quality_Category,isnull(SUM(CASE WHEN CallScore>=91 THEN 1 ELSE 0 END),0) Number_of_Agents,
	isnull(Round((Convert(decimal(10,2),SUM(CASE WHEN CallScore>=91 THEN 1 ELSE 0 END)*100))/@TotalAgent,2),0) per_Contribution
	FROM #temp where CallScore>=95
	UNION ALL
	SELECT ''2'',''B (Average)'' Call_Quality_Category,isnull(SUM(CASE WHEN CallScore>=86 and CallScore<=90 THEN 1 ELSE 0 END),0) ,
	isnull(Round((Convert(decimal(10,2),SUM(CASE WHEN CallScore>=86 and CallScore<=90 THEN 1 ELSE 0 END)*100))/@TotalAgent,2),0) per_Contribution
	FROM #temp where CallScore>=91 and CallScore<=94
	UNION ALL
	SELECT ''3'',''C (Moderate)'' Call_Quality_Category,isnull(SUM(CASE WHEN CallScore<86 THEN 1 ELSE 0 END),0) ,
	isnull(Round((Convert(decimal(10,2),SUM(CASE WHEN CallScore<86 THEN 1 ELSE 0 END)*100))/@TotalAgent,2),0) per_Contribution
	FROM #temp where CallScore<90;'
	
	EXEC(@SQL)
	PRINT(@SQL)

END

GO

/****** Object:  StoredProcedure [dbo].[QA_AgentDashboard]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE Proc [dbo].[QA_AgentDashboard]
@Pid varchar(10),@AgentName varchar(70)
-- QA_AgentDashboard 1,'Test'
as begin 

Declare @Sqlquery varchar(max),@QAFiled varchar(2000),@QACustDisposition varchar(200)
select @QACustDisposition=coalesce(@QACustDisposition+',','')+''''+CallStatus+'''' from QA_Page_Master1 where Process_ID=@Pid

select @QAFiled=COalesce(@QAFiled+',','')+'PM1.'+Field_Name+QUOTENAME(Field_Description) from Process_Master where Pid=@Pid and Show_In_App='1'
select @QAFiled=COalesce(@QAFiled+',','')+'Pl1.'+Field_Name+QUOTENAME(Field_Description) from Process_log where Pid=@Pid and Show_In_App='1'
--select @QAFiled
  set @Sqlquery='select /*pm1.Master_Id,Pl1.Log_id*/pl1.Srno,pm1.Phone_no,'+@QAFiled+' 
 from Process_'+@Pid+'_Master PM1 join (select max(Log_id)Lastlog_id,master_id from Process_'+@pid+'_log 
 where CallStatus in('+@QACustDisposition+') and  QA_Disposition in(''TL CALL'')  
 group by master_id)LPL on LPL.master_id=PM1.master_id
 Join Process_'+@Pid+'_Log PL1 on LPL.Lastlog_id=Pl1.Log_id where isnull(QA_Assign_TLAgent,'''')='''+@AgentName+'''  '
exec (@Sqlquery)
end

GO

/****** Object:  StoredProcedure [dbo].[QA_AuditWISE_REPORT]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





--QA_AuditWISE_REPORT '0','0','0','','','0','4','0'

CREATE PROC [dbo].[QA_AuditWISE_REPORT]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

DECLARE @sql NVARCHAR(MAX)='';
declare @LeadCallStatus varchar(50);
select @LeadCallStatus=Lead_Disposition from QA_Page_Master1 where process_id=@Process_id;

SET @sql='select convert(varchar,Date_of_Audit,103)Date_of_Audit, ql.Assign_To[QA Name],
count(Assign_To)[Total Lead Assign],
count(qa.Disposition)[Total leads audited],
(count(Assign_To)-count(qa.Disposition))[Audit pending],
sum(case when qa.Disposition=''Quality Approve'' then 1 else 0 end)[Audit ok],
sum(case when qa.Disposition in (''Quality Rework'',''Quality Hold'') then 1 else 0 end)[Rework],
sum(case when qa.Disposition=''Quality Reject'' then 1 else 0 end)[Reject]
from QA_AgentDetails qa 
join QA_Assign_Log ql on ql.Log_Id=qa.Log_Id
where P_id='''+@Process_id+'''  and isnull(ql.Assign_To,'''')!='''' and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) '
    IF(@tlid!='0')
    SET @sql+=' and qa.TLname='''+@tlid+'''';
    IF(@agentid!='0')
    SET @sql+=' and qa.Agent_Name='''+@agentid+'''';
    IF(@qaid!='0')
	SET @sql+=' and ql.Assign_To='''+@qaid+'''';
    SET @sql+='';

    SET @sql+=CASE WHEN @from='' THEN ' AND  CONVERT(DATE,Date_of_Audit)>=CONVERT(DATE,GETDATE()) ' ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END;
		SET @sql+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END;

SET @sql+=' group by ql.Assign_To,convert(varchar,Date_of_Audit,103) order by convert(varchar,Date_of_Audit,103),ql.Assign_To';
EXECUTE sp_executesql @SQL
Print(@SQL)

END



--select * from QA_Assign_Log
GO

/****** Object:  StoredProcedure [dbo].[QA_COUNT]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROC [dbo].[QA_COUNT] @Pid varchar(10),@FromDate varchar(19)='',@ToDate varchar(19)='',@User_Type varchar(30),@QAAgent varchar(70)='',@Suserid varchar(40)='',@Agent varchar(30)=''
--QA_COUNT '1','','','Quality Manager','',''
--QA_COUNT '1','2019-11-01','','Quality Manager','',''
--QA_COUNT @Pid='1',@FromDate='',@ToDate='',@User_Type='',@QAAgent='',@Suserid=''
--QA_COUNT @Pid='1',@User_Type='M'
-- QA_COUNT '1','','','Quality TL','','dev'
--QA_COUNT '1','2019-11-07','','TL','','SUmit'
--QA_COUNT  @Pid=1,@FromDate='2019-11-07',@ToDate='2019-11-08',@User_Type='T',@QAAgent='--select--'
--QA_COUNT '1','','','A','','dev'
As begin



Declare @Sqlquery1 varchar(max),@Sqlquery2 varchar(max),@CusDispotion varchar(max),@Sqlquery3 varchar(max),@DataCon varchar(max),@DataCon1 varchar(max),@SqlCon varchar(max),@StartDateFrom varchar(19)--='2019-11-09'
	select @CusDispotion=''''+replace(CallStatus,',',''',''')+'''',@StartDateFrom=Convert(varchar(10),Qa_Start_Date ,121) from QA_Page_Master1 where Process_id=@Pid
	set @DataCon=' and process_' + @Pid + '_log.Start_time>='''+@StartDateFrom+''' '+case when @FromDate='' then '  and  process_' + @Pid + '_log.Start_time>='''+Convert(varchar(10),getdate(),121)+' 00:00:00''' else  ' and process_' + @Pid + '_log.Start_time>='''+@FromDate+''''  end+ case when @ToDate='' then ' and process_' + @Pid + '_log.Start_time<='''+Convert(varchar(10),getdate(),121)+' 23:59:59''' else  ' and process_' + @Pid + '_log.Start_time<='''+@ToDate+''''  end 
	set @DataCon1= ' and QA_Assign_Log.Assign_Date>='''+@StartDateFrom+''''  +case when @FromDate='' then ' and QA_Assign_Log.Assign_Date >='''+Convert(varchar(10),getdate(),121)+' 00:00:00''' else  ' and QA_Assign_Log.Assign_Date>='''+@FromDate+''''  end+ case when @ToDate='' then ' and QA_Assign_Log.Assign_Date<='''+Convert(varchar(10),getdate(),121)+' 23:59:59''' else  ' and QA_Assign_Log.Assign_Date<='''+@ToDate+''''  end 

	set @SqlCon=' and QA_AgentDetails.Date_of_Audit>='''+@StartDateFrom+''' '+ case when @FromDate='' then ' and  QA_AgentDetails.Date_of_Audit>='''+Convert(varchar(10),getdate(),121)+' 00:00:00''' else  ' and QA_AgentDetails.Date_of_Audit>='''+@FromDate+''''  end+ case when @ToDate='' then ' and QA_AgentDetails.Date_of_Audit<='''+Convert(varchar(10),getdate(),121)+' 23:59:59''' else  ' and QA_AgentDetails.Date_of_Audit<='''+@ToDate+''''  end 
	
if(@Agent not in ('','--Select--'))
set @SqlCon=' and process_' + @Pid + '_log.Agent_name = '''+@Agent+''''
--if(@Disposition not in ('','--Select--'))
--set @Disposition=' and process_' + @Pid + '_log.CallStatus = '''+@Disposition+''''


	--select @CusDispotion,@StartDateFrom
	if @User_Type='Quality Manager'
	begin
	
	set @Sqlquery1='select count(1) [Total] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id=process_' + @Pid + '_log.Master_Id  where  CallStatus in('+@CusDispotion+') '+@DataCon+ case when @QAAgent='' or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +
	case when @Agent='' or @Agent='--select--'  then ' ' else ' and process_' + @Pid + '_log.Agent_name='''+@Agent+'''' end +' ;

					select count(1) [Pending] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id=process_' + @Pid + '_log.Master_Id where CallStatus in('+@CusDispotion+')  AND process_' + @Pid + '_log.QA_Disposition is null and Start_time>='''+@StartDateFrom +''''+ case when @QAAgent='' or @QAAgent='--select--'  then ' and status is null ' else ' and status=''1'' and QA_Assign_Agent='''+@QAAgent+'''' end +
					case when @Agent='' or @Agent='--select--'  then ' ' else ' and process_' + @Pid + '_log.Agent_name='''+@Agent+'''' end +' ;

	                select count(1) [Quality Approve] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Approve''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [Quality Rework] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Rework''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [Quality Hold] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Hold''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
	                select count(1) [Quality Reject] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Reject''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
				    select count(1) [Quality Pending] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id=process_' + @Pid + '_log.Master_Id  where   QA_Disposition Is Null and CallStatus in('+@CusDispotion+') '+@DataCon+ case when @QAAgent='' or @QAAgent='--select--'  then ' ' else '  and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [TL Approve] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Approve''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [TL Hold] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Hold''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [TL Call] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Call''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [Rework Done] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Rework Done''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [TL Reject] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Reject''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ; '
	  
	   -- exec  (@Sqlquery1)      
	end

else if @User_Type='Quality TL'
	begin
	Declare @TlId varchar(20)
	select @TlId=User_Id from User_Master where User_Name=@Suserid;
	 set @Sqlquery1='select count(1) [Total] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id=process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id where CallStatus in('+@CusDispotion+')  and (status is null or status=1) and (process_'+@Pid+'_log.QA_Disposition=''Quality Rework'' or  process_'+@Pid+'_log.QA_Disposition=''Quality Hold'') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+'''' + @SqlCon+';
					 select count(1) [Pending] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id=process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id where CallStatus in('+@CusDispotion+') and (process_'+@Pid+'_log.QA_Disposition=''Quality Rework'' or  process_'+@Pid+'_log.QA_Disposition=''Quality Hold'') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''' and QA_AgentDetails.Date_of_Audit>='''+@StartDateFrom +''';
					 select count(1) [Quality Rework] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''Quality Rework'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (process_'+@Pid+'_log.QA_Disposition=''Quality Rework'' or  process_'+@Pid+'_log.QA_Disposition=''Quality Rework'') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+ @TlId +'''' + @SqlCon+';
					 select count(1) [Quality Hold] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''Quality Hold'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (process_'+@Pid+'_log.QA_Disposition=''Quality Hold'' or  process_'+@Pid+'_log.QA_Disposition=''Quality Hold'') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''''+ @SqlCon+';
					 select count(1) [TL Approve] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on  process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''TL Approve'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''''+ @SqlCon+';
					 select count(1) [TL Hold] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''TL Hold'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''''+ @SqlCon+';
					 select count(1) [TL Call] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on  process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''TL Call'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''''+ @SqlCon+';
					 select count(1) [Rework Done] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''Rework Done'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''''+ @SqlCon+';
					 select count(1) [TL Reject] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''TL Reject'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''''+ @SqlCon+';'
		--exec(@Sqlquery1)
	end
if @User_Type='Quality Agent'
	begin
	
	set @Sqlquery1='select count(1)[Total] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id=process_'+@Pid+'_log.Master_Id  join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid  where CallStatus in('+@CusDispotion+')  and status=1 and QA_Assign_Agent='''+@Suserid+''''+ @DataCon1+';
					select count(1)[Pending] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id=process_'+@Pid+'_log.Master_Id  join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid  where CallStatus in('+@CusDispotion+') and  (process_'+@Pid+'_log.QA_Disposition is null ) and QA_Assign_Agent='''+@Suserid+''' and QA_Assign_Log.Assign_Date>='''+@StartDateFrom +''' ;
					select count(1)[Quality Approve] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id   join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid      Left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where  Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and process_'+@Pid+'_log.QA_Disposition = ''Quality Approve''  and QA_Assign_Agent='''+@Suserid+''''+ @SqlCon+';
					select count(1)[Quality Rework] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id    join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid      Left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where  Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and process_'+@Pid+'_log.QA_Disposition = ''Quality Rework''  and QA_Assign_Agent='''+@Suserid+''''+ @SqlCon+';
					select count(1)[Quality Hold] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id		   join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid    Left Join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where  Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and process_'+@Pid+'_log.QA_Disposition = ''Quality Hold''  and QA_Assign_Agent='''+@Suserid+''''+ @SqlCon+';
					select count(1)[Quality Reject] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id     join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid     Left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where  Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and process_'+@Pid+'_log.QA_Disposition = ''Quality Reject'' and QA_Assign_Agent='''+@Suserid+''''+ @SqlCon+'; 
					select count(1)[TL Approve] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id		      join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid Left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where  Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and process_'+@Pid+'_log.QA_Disposition = ''TL Approve'' and QA_Assign_Agent='''+@Suserid+''''+ @SqlCon+';
					select count(1)[TL Hold] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Hold''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1)[TL Call] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Call''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1)[Rework Done] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Rework Done''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1)[TL Reject] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Reject''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ; '
          

		 --select (@Sqlquery3)
	  --exec (@Sqlquery1)      
	end
	exec(@Sqlquery1)
	print(@Sqlquery1)
end



GO

/****** Object:  StoredProcedure [dbo].[QA_Defect_Analysis_Report]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






--QA_Defect_Analysis_Report '0','0','0','','','0','1','0'
CREATE PROC [dbo].[QA_Defect_Analysis_Report]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

	DECLARE @SQL VARCHAR(MAX)
	
	set @SQL='SELECT ROW_NUMBER() OVER (ORDER BY SUM(CASE WHEN Rating=''No'' THEN 1 ELSE 0 END) desc)RN,Question,SUM(CASE WHEN Rating=''No'' THEN 1 ELSE 0 END) [Defect_Count]
			  /*,SUM (SUM(CASE WHEN Rating=''No'' THEN 1 ELSE 0 END)) OVER (ORDER BY (SUM(CASE WHEN Rating=''No'' THEN 1 ELSE 0 END))desc)[Cumulative_Frequency]
			  ,(SUM (SUM(CASE WHEN Rating=''No'' THEN 1 ELSE 0 END)) OVER (ORDER BY (SUM(CASE WHEN Rating=''No'' THEN 1 ELSE 0 END))desc)*100.00)/nullif(SUM(CASE WHEN Rating=''No'' THEN 1 ELSE 0 END),0)[per_Cumulative_Frequency]*/
			  into #temp
			  from QA_MonitoringFormDetails QMFD join QA_AgentDetails QAA ON QMFD.Q_id=QAA.Q_id where Rating=''No'' and 1=1'
			  IF(@tlid!='0')
				SET @sql+=' AND TLname='''+@tlid+''' ';
			  IF(@agentid!='0')
				SET @sql+=' AND Agent_Name='''+@agentid+''' ';
			  IF(@qaid!='0')
			  SET @sql+=' AND Auditor_Name='''+@qaid+''' ';
			  SET @sql+=CASE WHEN @from='' THEN ' AND CONVERT(DATE,Date_of_Audit)>=CONVERT(DATE,Getdate()) ' ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END;
			  SET @sql+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END;
			  set @SQL+='group by Question
						 order by (SUM(CASE WHEN Rating=''No'' THEN 1 ELSE 0 END)) desc
	
	DECLARE @TOTAL int
	select @TOTAL=SUM(Defect_Count) from #temp
	
	select RN SrNo,Question,[Defect_Count],SUM (SUM([Defect_Count])) OVER (ORDER BY RN)[Cumulative_Frequency],
	(SUM (SUM([Defect_Count])) OVER (ORDER BY RN)*100)/@TOTAL [per_Cumulative_Frequency] from #temp
	group by RN,Question,[Defect_Count]'
	
	exec(@SQL)
	print(@SQL)

END



GO

/****** Object:  StoredProcedure [dbo].[QA_DQR_Report]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- QA_DQR_Report '','','','2021-05-13','','','4',''

CREATE PROC [dbo].[QA_DQR_Report]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)

AS
BEGIN

Declare @Question nvarchar(max),@sql nvarchar(max),@Question_Name nvarchar(max),@Master_Field Varchar(max),@Log_Field Varchar(max),@ffrom varchar(20),@fto varchar(20);
set @ffrom=@from;
set @fto=@to;

set @from=case when @from='' then COnvert(varchar(10),getdate(),121) else @from end ;
set @to=case when @to='' then COnvert(varchar(10),getdate()+1,121) else @to end ;

Select @Question= COALESCE (@Question + ',','') + 'MAX(CASE WHEN Question='''+QUOTENAME(Convert(varchar(300),Question))+''' THEN Rating END) ' +QUOTENAME(Convert(varchar(300),Question)) from QA_Page_Master where process_id=@Process_id  order by id ASC;
select @Question_Name=COALESCE(@Question_Name + ',','')+QUOTENAME(Convert(varchar(128),Question)) from QA_Page_Master where process_id=@Process_id  order by id ASC;
Select @Master_Field= COALESCE (@Master_Field + ',','') + 'Pm.'+QUOTENAME(Field_Name) from Process_master where pid=@Process_id and Show_In_TMS=1 order by Order_No;
Select @Log_Field= COALESCE (@Log_Field + ',','') + 'Pl.'+QUOTENAME(Field_Name) from Process_log where pid=@Process_id and Send_By_EMail=1 order by Order_No;

-- QA_DQR_Report '','','','2021-05-13','','','4',''
--select @Master_Field
set @sql='
select Q_id,'+@Question+' into #temp from QA_MonitoringFormDetails group by  Q_id 

select QAD.Log_id,QAD.Agent_Name,CallerEMPID [Agent_Code],TLname [Team_Leader_Name],Date_of_Call [Call_date],Auditor_Name,Date_of_Audit,
'+isnull(@Master_Field,'')+','+isnull(@Log_Field,'')+',Date_of_Audit [Verifier_Date], Disposition [Call_Status],QAD.Agent_Name[Call_taken_by],'+@Question_Name+',FTR FTR_Status
,External_performance[Score_without_Fatal],Internal_Performance[Score_with_Fatal],QAD.call_summary,QAD.areas_of_strength,QAD.areas_of_development,QAD.TL_Remarks
from QA_AgentDetails QAD
left join Process_'+@Process_id+'_log pl on QAD.log_id=pl.Log_Id
left join Process_'+@Process_id+'_Master PM on pl.Master_Id=pm.Master_Id
left join #temp QMFD on qmfd.Q_id=qad.Q_id
where convert(date,QAD.Date_of_Audit)>='''+@from+''' and convert(date,QAD.Date_of_Audit)<='''+@to+''' '+case when isnull(@tlid,'')!='' and @tlid!='0' then ' and QAD.TLname='''+@tlid+''' ' else '' end 
+case when isnull(@agentid,'')!=''and @agentid!='0' then ' and QAD.Agent_Name='''+@agentid+''' ' else '' end 
+case when isnull(@qaid,'')!=''and @qaid!='0' then ' and QAD.Auditor_Name='''+@qaid+''' ' else '' end +' 
order by QAD.Date_of_Audit asc'
exec (@sql)
print(@to)
if  object_id('webcrm.dbo.traceDQR') is null
begin
Create table traceDQR(tlid VARCHAR(30), agentid VARCHAR(30), qaid VARCHAR(30), Qfrom VARCHAR(10), Qto VARCHAR(10), reporttype VARCHAR(30),Process_id VARCHAR(10),UserId VARCHAR(10),Qsql varchar(max))

end
Insert into traceDQR(tlid, agentid, qaid, Qfrom , Qto, reporttype,Process_id,UserId,Qsql) values (@tlid, @agentid, @qaid, @ffrom, @fto, @reporttype,@Process_id,@UserId,@sql);

END



GO

/****** Object:  StoredProcedure [dbo].[QA_FTR_Status_Report]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--exec QA_FTR_Status_Report '0','0','0','20210401','20210430','FTRStatus','1','1','4','True'
--exec QA_FTR_Status_Report '0','0','0','20210401','20210430','FTRStatus','1','1','4','False'
CREATE proc [dbo].[QA_FTR_Status_Report]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),
@Process_id VARCHAR(10),@UserId VARCHAR(10),@month varchar(10),@isweekwise varchar(10)

AS BEGIN

if  object_id('tempdb..#temp1') is not null
begin
	drop table #temp1;
end

if  object_id('tempdb..#Monthdate') is not null
begin
	drop table #Monthdate;
end
if  object_id('tempdb..#temp') is not null
begin
	drop table #temp;
end

if(@month='')
set @month= CONVERT(varchar,( DATEPART(month,getdate())))
	
	declare @LeadCallStatus varchar(50);
	select @LeadCallStatus=Lead_Disposition from QA_Page_Master1 where process_id=@Process_id;

DECLARE @SQL VARCHAR(MAX)
declare @cond1 varchar(500)='';
declare @cond2 varchar(500)='';
declare @Date varchar(10),@day int
set @Date=Convert(varchar,datepart(year,GETDATE()))+'-'+@month+'-01'
set @day=datediff(day,DATEADD(mm, DATEDIFF(mm, 0, @Date), 0), DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, @Date) + 1, 0)))
;with cte as (select 0 Nday
union all
select Nday+1 Nday from cte where Nday<@day)
select NTILE(4)over( order by  Nday ) wk,Convert(date,DATEADD(day, Nday, @Date))Customdate into #Monthdate from cte ;


if(@isweekwise='True')
set @cond1+=' wk [Week],'
else
set @cond1+=' '

if(@isweekwise='True')
set @cond2+=' join #Monthdate md on md.Customdate=Convert(varchar(10),qa.Date_of_Audit,121) '
else
set @cond2+=' '

	set @SQL='select ROW_NUMBER() OVER (Order by pl.Agent_Name)SrNo,pl.Agent_Name,'+@cond1+' Emp_Code,
			  SUM(CASE WHEN Callstatus='''+@LeadCallStatus+''' THEN 1 ELSE 0 END)[Total Sale],SUM(CASE WHEN FTR=''Yes'' THEN 1 ELSE 0 END)[FTR_Status],
			  (SUM(CASE WHEN FTR=''Yes'' THEN 1 ELSE 0 END)*100)/nullif(SUM(CASE WHEN Callstatus='''+@LeadCallStatus+''' THEN 1 ELSE 0 END),0) FTR_Status_per
			  into #temp1 from QA_AgentDetails qa '+@cond2+' right join Process_'+@Process_id+'_log pl on qa.log_id=pl.Log_Id
			  left join Admin_Agent_Master on Admin_Agent_Master.Agent_name=pl.agent_Name where 1=1'
		IF(@tlid!='0')
		SET @sql+=' AND TLname='''+@tlid+''' ';
		IF(@agentid!='0')
		SET @sql+=' AND pl.Agent_Name='''+@agentid+''' ';
		IF(@qaid!='0')
		SET @sql+=' AND Auditor_Name='''+@qaid+''' ';

		if(@isweekwise!='True')
		begin
		SET @sql+=CASE WHEN @from='' THEN ' AND  CONVERT(DATE,Date_of_Audit)>=CONVERT(DATE,Getdate()) '
		 ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END;
		SET @sql+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' 
		ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END;
		end
		else begin 
		SET @sql+=' '
		end

		if(@isweekwise!='True')
	begin
	set @SQL+=' group by pl.Agent_Name,Emp_Code;'
	end
	else begin
	set @SQL+=' group by pl.Agent_Name,Emp_Code,wk;'
	end


	
	if(@isweekwise!='True')
	begin
	set @SQL+='select SrNo,Agent_Name,Emp_Code,[Total Sale],[FTR_Status],FTR_Status_per from #temp1
	UNION ALL
	select NULL,''Grand Total'','''',SUM([Total Sale]),SUM([FTR_Status]),(SUM(FTR_Status)*100)/nullif(SUM([Total Sale]),0) from #temp1 '
	end else
	begin 
	set @SQL+='select SrNo,[week],Agent_Name,Emp_Code,[Total Sale],[FTR_Status],FTR_Status_per from #temp1
	UNION ALL
	select NULL,NULL,''Grand Total'','''',SUM([Total Sale]),SUM([FTR_Status]),(SUM(FTR_Status)*100)/nullif(SUM([Total Sale]),0) from #temp1 '
	end




	set @SQL+='select ROW_NUMBER() OVER (Order by Case WHEN GROUPING(TlName)=1 THEN 1 Else 0 End)SrNo,'+@cond1+' Case WHEN GROUPING(TlName)=1 THEN ''Grand Total'' Else TlName End TL_Name,
				SUM(CASE WHEN Callstatus='''+@LeadCallStatus+''' THEN 1 ELSE 0 END)[Total Sale],SUM(CASE WHEN FTR=''Yes'' THEN 1 ELSE 0 END)[FTR_Status],
				(SUM(CASE WHEN FTR=''Yes'' THEN 1 ELSE 0 END)*100)/nullif(SUM(CASE WHEN Callstatus='''+@LeadCallStatus+''' THEN 1 ELSE 0 END),0) FTR_Status_per
			   from QA_AgentDetails qa '+@cond2+' right join Process_'+@Process_id+'_log pl on qa.log_id=pl.Log_Id where 1=1 '
		IF(@tlid!='0')
		SET @sql+=' AND TLname='''+@tlid+''' ';
		IF(@agentid!='0')
		SET @sql+=' AND pl.Agent_Name='''+@agentid+''' ';
		IF(@qaid!='0')
		SET @sql+=' AND Auditor_Name='''+@qaid+''' ';



		if(@isweekwise!='True')
		begin
		SET @sql+=CASE WHEN @from='' THEN ' AND  CONVERT(DATE,Date_of_Audit)>=CONVERT(DATE,Getdate()) '
		 ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END;
		SET @sql+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' 
		ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END;
		end
		else begin 
		SET @sql+=' '
		end


		if(@isweekwise!='True')
		begin
		set @SQL=@SQL+'group by TLname with Cube;'
		end else
		begin
		set @SQL=@SQL+'group by wk,TLname with Cube;'
		 end

	

	set @SQL+='SELECT ROW_NUMBER() OVER (Order by TLname)SrNo,'+@cond1+' TLname TL_Name,AVG(External_Performance) Average_of_Call_Score_per into #temp from QA_AgentDetails qa '+@cond2+' WHERE 1=1 '
	IF(@tlid!='0')
		SET @sql+=' AND TLname='''+@tlid+''' ';
		IF(@agentid!='0')
		SET @sql+=' AND Agent_Name='''+@agentid+''' ';
		IF(@qaid!='0')
		SET @sql+=' AND Auditor_Name='''+@qaid+''' ';


		if(@isweekwise!='True')
		begin
		SET @sql+=CASE WHEN @from='' THEN ' AND  CONVERT(DATE,Date_of_Audit)>=CONVERT(DATE,Getdate()) '
		 ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END;
		SET @sql+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' 
		ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END;
		end
		else begin 
		SET @sql+=' '
		end


		if(@isweekwise!='True')
		begin
			set @SQL=@SQL+'group by TLname;'
		end
		else 
		begin
			set @SQL=@SQL+'group by wk,TLname;'
		 end


		 if(@isweekwise!='True')
		begin
	set @SQL+='SELECT SrNo,TL_Name,Average_of_Call_Score_per from #temp 
	UNION ALL
	SELECT NULL,''AVG_Total'',SUM(Average_of_Call_Score_per)/nullif(COUNT(*),0)  from #temp ;'
	end
	else begin 
		set @SQL+='SELECT SrNo,[week],TL_Name,Average_of_Call_Score_per from #temp 
	UNION ALL
	SELECT NULL,NULL,''AVG_Total'',SUM(Average_of_Call_Score_per)/nullif(COUNT(*),0)  from #temp ;'
	end

	EXEC(@SQL)
	PRINT(@SQL)
END

GO

/****** Object:  StoredProcedure [dbo].[QA_LogDetails]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO








CREATE PROC [dbo].[QA_LogDetails]

--QA_Manger '0','100','1','2018-10-10','','munish','TOTAL'
@LogID varchar(10)

AS BEGIN
DECLARE @sql varchar(max)

	set @sql='select Q_id,P_id,Agent_Name [Agent Name],Auditor_Name [Auditor Name],Customer_Name [Customer Name],Date_of_Call [Date of Call],Date_of_Audit [Date of Audit],Mobile [Mobile],Brand [Brand],Month,AHT,External_Performance [External Performance],Internal_Performance [Internal Performance],Fatal_Error_Call [Fatal Error Call],call_summary [Call Summary],areas_of_strength [Areas of strength],areas_of_development [Areas OfD evelopment],RecordingUrl [Recording Url],CallerEMPID [Caller EMPID],TLname [TL Name],Disposition [Disposition],ManagerName [Manager Name],LeadID [Lead ID],LeadStatusTime [Lead Status Time],Audit,CallDuration [Call Duration],Week,LeadSource [Lead Source],EvaluationMode [Evaluation Mode],ACPT,Log_Id,time_stamp from QA_AgentDetails where Log_Id = '+@LogID+''
	
	print(@sql)
	exec (@sql)
	
END


--[QA_LogDetails] '43692'


GO

/****** Object:  StoredProcedure [dbo].[QA_LogDetailsWithQ_ID]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






CREATE PROC [dbo].[QA_LogDetailsWithQ_ID]

--QA_Manger '0','100','1','2018-10-10','','munish','TOTAL'
@QID varchar(10)
--@LogID varchar(10)
AS BEGIN
DECLARE @sql varchar(max)

	set @sql='select case when RowN=1 then Parameter_Name else '''' end [Parameter Name],status,Question,Weightage,Applicable_Weightage [Applicable Weightage],Agents_Grade[Agents Grade],Rating,QA_comment[QA comment] from ( Select
ROW_NUMBER() over(partition by Parameter_Name order by Parameter_Name) RowN ,Q_ID,
Parameter_Name ,status,Question,Weightage,Applicable_Weightage ,Agents_Grade,Rating,QA_comment from QA_MonitoringFormDetails where Q_ID = '+@QID+') as Table1;
select 
call_summary from QA_AgentDetails  where Q_ID = '+@QID+';
select 
areas_of_strength from QA_AgentDetails  where Q_ID = '+@QID+';
select 
areas_of_development from QA_AgentDetails  where Q_ID = '+@QID+';
select 
TL_Remarks from QA_AgentDetails  where Q_ID = '+@QID+''
	
	print(@sql)
	exec (@sql)
	
END


--[QA_LogDetailsWithQ_ID] '3014'



GO

/****** Object:  StoredProcedure [dbo].[QA_Manger]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE proc [dbo].[QA_Manger]

--QA_Manger '0','100','1','','','','NOT ASSIGN'
@pageindex varchar(20),@pagesize varchar(20),@Pid varchar(10),@FromDate varchar(10)='',@Todate varchar(19)='',@Agent_name varchar(70)='',@Type varchar(30),@Agent varchar(30)='',@Disposition varchar(30),@SubDisposition varchar(30)

AS BEGIN
DECLARE @sql varchar(max),@CusDispotion varchar(max),@StartDateFrom varchar(19)='',@List VARCHAR(max),@List1 VARCHAR(max),@datefield varchar(100);

select @StartDateFrom=Convert(varchar(10),Qa_Start_Date ,121) from QA_Page_Master1 where Process_id=@Pid


if (@Agent_name= '--select--')
set @Agent_name=''


select @List=COALESCE(@List + ',', '')+ 'MasterData.'+Field_Name+QUOTENAME(Field_Description) from process_master where pid=@Pid and Show_In_TMS is not null and Show_In_TMS != 0 and
Field_Type not in ('I-Frame')

select @List1=COALESCE(@List1 + ',', '')+ 'Calllog.'+Field_Name+QUOTENAME(Field_Description) from process_log where pid=@Pid and Send_By_EMail is not null and Send_By_EMail != 0
set @List=@List+Case when isnull(@List1,'')!='' then ','+@List1 else '' end

set @datefield=case when  @Type='NOT ASSIGN' THEN ' ' WHEN @Type in('TOTAL','','Quality Pending') THEN ' Start_time >='''+@StartDateFrom+''' and convert(date,Start_Time)' ELSE ' Date_of_Audit >='''+@StartDateFrom+''' and convert(date,Date_of_Audit)' END
set @FromDate=case when @FromDate='' then convert(date,getdate()) else @FromDate end
set @Todate=case when @Todate='' then convert(date,getdate()) else @Todate end
select @CusDispotion=''''+replace(CallStatus,',',''',''')+'''' from QA_Page_Master1 where Process_id=@Pid

    set @sql='select Q_id,Calllog.Log_id ,MasterData.Master_ID,Calllog.Log_id[Log Id],Calllog.Agent_Name,Admin_Agent_Master.Emp_Code,Calllog.Start_Time,Calllog.End_Time,'+@List+',CallStatus,SAN_Sub_Disposition[Sub Disposition],QA_AgentDetails.Disposition,QA_Assign_Agent [QA Name],'+@Pid+'[PID]  into #tempp from process_'+@Pid+'_master MasterData
    left join process_'+@Pid+'_log Calllog on Calllog.Master_Id=MasterData.master_id
	Left Join Campaign_master on Calllog.Campaign_id=Campaign_master.Campaign_id
	Left Join Admin_Agent_Master on Calllog.Agent_name=Admin_Agent_Master.Agent_name
    left join QA_AgentDetails on calllog.Log_id=QA_AgentDetails.Log_id
     where  Calllog.CallStatus in('+@CusDispotion+') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null)   '

    if @Type='NOT ASSIGN'
         set @sql+='and Start_time>='''+@StartDateFrom +'''';
     if @Type not in('NOT ASSIGN','total','','Quality Pending')
         set @sql+=' and Calllog.QA_Disposition = '''+@Type+''' '
     if(@Type in('Quality Pending') )
     set @sql+='and (Calllog.QA_Disposition is null )';

     if(@Type in('')) AND (@Agent_name!='')
     set @sql+='and (Calllog.QA_Disposition is null )';
     else if(@Type='') AND (@Agent_name='')
     set @sql+='and (Calllog.QA_Disposition is null and Calllog.Status is null )';

     if(@datefield!='')
     set @sql+=' AND '+@datefield+' BETWEEN '''+@FromDate+''' AND '''+@Todate+''' '

     else
     set @sql+=' and Calllog.QA_Disposition is null   '

      If (@Agent_name not in ('','--select--'))
        set @sql+= ' and QA_Assign_Agent ='''+@Agent_name+''' '
     if(@Type='NOT ASSIGN' )
        Set @sql+=' and Calllog.status is null '


        --By Chetan on 31Aug2020
        if(@Agent not in ('','--select--'))
            Set @sql+=' and Calllog.Agent_Name ='''+@Agent + ''' '
		if(@Disposition not in ('','--select--'))
            Set @sql+=' and Calllog.CallStatus ='''+@Disposition + ''' '
			if(@SubDisposition not in ('','--select--','1','0'))
            Set @sql+=' and Calllog.SAN_Sub_Disposition ='''+@SubDisposition + ''' '


 set @sql+='   ; Select * from #tempp order by Log_id
    offset '+@pageindex+' rows fetch next '+@pagesize+' row only
    select count(*) from #tempp '
    print(@sql)
    exec  (@sql)

END

GO

/****** Object:  StoredProcedure [dbo].[QA_Manger_AgentWise]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE proc [dbo].[QA_Manger_AgentWise]

--QA_Manger_AgentWise '0','100','1','','','munish',''
@pageindex varchar(20),@pagesize varchar(20),@Pid varchar(10),@FromDate varchar(10)='',@Todate varchar(19)='',
@user varchar(20),@Type varchar(30),@Disposition varchar(30)='',@SubDisposition varchar(30)=''

AS BEGIN
DECLARE @sql varchar(max),@CusDispotion varchar(2000),@PandingDateFrom varchar(19)='',@List VARCHAR(max),@List1 VARCHAR(max),@datefield varchar(50);

select @List=COALESCE(@List + ',', '')+ 'MasterData.'+Field_Name+QUOTENAME(Field_Description) from process_master where pid=@Pid and Show_In_TMS is not null and Show_In_TMS != 0

select @List1=COALESCE(@List1 + ',', '')+ 'Calllog.'+Field_Name+QUOTENAME(Field_Description) from process_log where pid=@Pid and Send_By_EMail is not null and Send_By_EMail != 0
set @List=@List+Case when isnull(@List1,'')!='' then ','+@List1 else '' end
select @CusDispotion=''''+replace(CallStatus,',',''',''')+'''' from QA_Page_Master1 where Process_id=@Pid

--select 'MasterData.'+Field_Name[Field_Name] into #ActiveField from process_master where pid=@Pid and Show_In_App is not null and Show_In_App != 0
--Union
--select 'Calllog.'+Field_Name[Field_Name] from process_log where pid=@Pid and Show_In_App is not null and Show_In_App != 0

--DECLARE @List VARCHAR(8000),@datefield varchar(50); SELECT @List = COALESCE(@List + ',', '') + CAST(Field_Name AS VARCHAR) FROM  #ActiveField ; 
--select @List

set @datefield=case when  @Type='PENDING' THEN '' WHEN @Type in('TOTAL','') THEN 'convert(date,QA_Assign_Log.Assign_Date)' ELSE 'convert(date,Date_of_Audit)' END
set @FromDate=case when @FromDate='' then convert(date,getdate()) else @FromDate end
set @Todate=case when @Todate='' then convert(date,getdate()) else @Todate end

	set @sql='select Calllog.Log_id,MasterData.Master_Id,Calllog.Log_id[Log Id],Calllog.Agent_Name,Admin_Agent_Master.Emp_Code,Campaign_master.Campaign_Name,Calllog.Start_Time,Calllog.End_Time,'+@List+',CallStatus,SAN_Sub_Disposition[Sub Disposition],Calllog.CallType,QA_AgentDetails.Disposition,QA_Assign_Log.Assign_Date[Assign Date],'+@Pid+'[PID]  into #tempp from process_'+@Pid+'_master MasterData 
	Join process_'+@Pid+'_log Calllog on Calllog.Master_Id=MasterData.master_id
	Left Join Campaign_master on Calllog.Campaign_id=Campaign_master.Campaign_id
	Left Join Admin_Agent_Master on Calllog.Agent_name=Admin_Agent_Master.Agent_name
	Join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  Calllog.Log_id=LastAssign.Log_id 
	Join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid
	Left join QA_AgentDetails on calllog.Log_id=QA_AgentDetails.Log_id
	 where  Calllog.CallStatus in('+@CusDispotion+') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null)  '
	 if @Type='NOT ASSIGN'
	 	set @sql+=' and QA_Assign_Log.Assign_Date>='''+@PandingDateFrom +''' ';
	 
	 if(@Type='')
	 set @sql+='and Calllog.QA_Disposition is null ';
	 if(@datefield!='')
	 set @sql+=' AND '+@datefield+' BETWEEN '''+@FromDate+''' AND '''+@Todate+''' '

	 else
	 set @sql+=' and Calllog.QA_Disposition is null   '

	 if(@datefield='convert(date,Date_of_Audit)')
	 set @sql+=' and Calllog.QA_Disposition = '''+@Type+''' '

	  If (@user  not in (''))
		set @sql+= ' and QA_Assign_Agent ='''+@user+''' '
		else
		if(@Type!='' and @Type!='total' AND @datefield!='convert(date,Date_of_Audit)')
		Set @sql+=' and status is null '

		if(@Disposition not in ('','--select--','1','0'))
            Set @sql+=' and Calllog.CallStatus ='''+@Disposition + ''' '

			if(@SubDisposition not in ('','--select--','1','0'))
            Set @sql+=' and Calllog.SAN_Sub_Disposition ='''+@SubDisposition + ''' '


 set @sql+='  Select * from #tempp order by Log_id
	offset '+@pageindex+' rows fetch next '+@pagesize+' row only 
	select count(*) from #tempp '
	print(@sql)
	exec (@sql)
	
END

GO

/****** Object:  StoredProcedure [dbo].[QA_Manger_TLWise]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE proc [dbo].[QA_Manger_TLWise]


--QA_Manger_TLWise '0','100','1','','','20',''
@pageindex varchar(20),@pagesize varchar(20),@Pid varchar(10),@FromDate varchar(10)='',@Todate varchar(19)='',@user varchar(20)
,@Type varchar(30),@Disposition varchar(30),@SubDisposition varchar(30)

AS BEGIN
DECLARE @sql varchar(max),@CusDispotion varchar(2000),@PandingDateFrom varchar(19)='',@List VARCHAR(max),@List1 VARCHAR(max),@datefield varchar(100),@cond varchar(200);

select @List=COALESCE(@List + ',', '')+ 'MasterData.'+Field_Name+QUOTENAME(Field_Description) from process_master where pid=@Pid and Show_In_TMS is not null and Show_In_TMS != 0

select @List1=COALESCE(@List1 + ',', '')+ 'Calllog.'+Field_Name+QUOTENAME(Field_Description) from process_log where pid=@Pid and Send_By_EMail is not null and Send_By_EMail != 0
set @List=@List+Case when isnull(@List1,'')!='' then ','+@List1 else '' end
select @CusDispotion=''''+replace(CallStatus,',',''',''')+'''' from QA_Page_Master1 where Process_id=@Pid

--DECLARE @List VARCHAR(8000),@datefield varchar(50),@cond varchar(200); SELECT @List = COALESCE(@List + ',', '') + CAST(Field_Name AS VARCHAR) FROM  #ActiveField ; 
--select @List

--set @datefield=case when  @Type='PENDING' THEN '' WHEN @Type in('TOTAL','') THEN 'convert(date,Start_Time)' ELSE 'convert(date,Date_of_Audit)' END
set @datefield='convert(date,Date_of_Audit)'
--set @datefield=case when  @Type='PENDING' THEN '' WHEN @Type in('TOTAL','') THEN  'convert(date,Date_of_Audit)' END
set @FromDate=case when @FromDate='' then convert(date,getdate()) else @FromDate end
set @Todate=case when @Todate='' then convert(date,getdate()) else @Todate end
set @cond=case when @Type='TOTAL'  then  ' and (Calllog.QA_Disposition=''Quality Rework'' or  Calllog.QA_Disposition=''Quality Hold'')' 
when @Type='PENDING'  then  ' and (Calllog.QA_Disposition=''Quality Rework'' or  Calllog.QA_Disposition=''Quality Hold'') and Team_Leader_Id ='''+@user+''''
when @Type='QUALITY REWORK'  then  '  and Calllog.QA_Disposition = ''Quality Rework'' and (Calllog.QA_Disposition=''Quality Rework'' or  Calllog.QA_Disposition=''Quality Hold'')'

when @Type='QUALITY HOLD'  then  ' and Calllog.QA_Disposition = ''Quality Hold'' and (Calllog.QA_Disposition=''Quality Rework'' or  Calllog.QA_Disposition=''Quality Hold'')'

when @Type='TL APPROVE'  then  '  and Calllog.QA_Disposition = ''TL Approve'''
when @Type='TL Hold'  then  '  and Calllog.QA_Disposition = ''TL Hold'''
when @Type='TL Call'  then  '  and Calllog.QA_Disposition = ''TL Call'''
when @Type='TL Rework Done'  then  ' and Calllog.QA_Disposition = ''Rework Done'''
when @Type='TL REJECT'  then  ' and Calllog.QA_Disposition = ''TL Reject'''


else  ' and Calllog.QA_Disposition != '''' and (Calllog.QA_Disposition=''Quality Rework'' or  Calllog.QA_Disposition=''Quality Hold'')' end

	set @sql='select Calllog.Log_id,MasterData.Master_Id,Calllog.Log_id[Log ID],Calllog.Agent_Name,Admin_Agent_Master.Emp_Code,Campaign_master.Campaign_Name,Calllog.Start_Time,Calllog.End_Time,'+@List+',CallStatus,SAN_Sub_Disposition[Sub Disposition],Calllog.CallType,QA_AgentDetails.Disposition,QA_Assign_Log.Assign_Date[Assign Date],Convert(varchar,QA_AgentDetails.Date_of_Audit,121)[Date of Audit],'+@Pid+'[PID] into #tempp from process_'+@Pid+'_master MasterData 
	Join process_'+@Pid+'_log Calllog on Calllog.Master_Id=MasterData.master_id
	Left Join Campaign_master on Calllog.Campaign_id=Campaign_master.Campaign_id
	Left Join Admin_Agent_Master on Calllog.Agent_name=Admin_Agent_Master.Agent_name
	Left join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  Calllog.Log_id=LastAssign.Log_id 
	Left join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid
	Left join QA_AgentDetails on calllog.Log_id=QA_AgentDetails.Log_id
	 where  QA_AgentDetails.Date_of_Audit>='''+@PandingDateFrom +''' and Calllog.CallStatus in('+@CusDispotion+') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null)  '
	 
	 if(@Type='')
	 set @sql+='';
	 if(@datefield!='' AND @Type!='PENDING')
	 set @sql+=' AND '+@datefield+' BETWEEN '''+@FromDate+''' AND '''+@Todate+''' '

	 ----else
	 --set @sql+=' and QA_Disposition is null   '
--' and QA_Disposition = '''+@Type+''' 
	 if(@datefield='convert(date,Date_of_Audit)')
	 set @sql+=@cond

	 
	  If (@user  not in (''))
		set @sql+= ' and Admin_Agent_Master.Team_Leader_Id ='''+@user+''' '
		else
		if(@Type!='' and @Type!='total' AND @datefield!='convert(date,Date_of_Audit)')
		Set @sql+=' and (status is null or status = 1) '

		if(@Disposition not in ('','--select--'))
            Set @sql+=' and Calllog.CallStatus ='''+@Disposition + ''' '
			if(@SubDisposition not in ('','--select--','1','0'))
            Set @sql+=' and Calllog.SAN_Sub_Disposition ='''+@SubDisposition + ''' '



 set @sql+='  Select * from #tempp order by Log_id
	offset '+@pageindex+' rows fetch next '+@pagesize+' row only 
	select count(*) from #tempp '
	print(@sql)
	exec (@sql)
	
END

GO

/****** Object:  StoredProcedure [dbo].[QA_MonitoringDetail]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





--QA_MonitoringDetail '4','1673555'
CREATE PROC [dbo].[QA_MonitoringDetail]
@Pid varchar(10),@log_id varchar(70)

AS BEGIN
DECLARE @sql varchar(max),@List VARCHAR(max),@List1 VARCHAR(max);


select @List=COALESCE(@List + ',', '')+ 'MasterData.'+Field_Name+QUOTENAME(Field_Description) from process_master where pid=@Pid and Show_In_TMS is not null and Show_In_TMS != 0

select @List1=COALESCE(@List1 + ',', '')+ 'Calllog.'+Field_Name+QUOTENAME(Field_Description) from process_log where pid=@Pid and Send_By_EMail is not null and Send_By_EMail != 0
set @List=@List+Case when isnull(@List1,'')!='' then ','+@List1 else '' end
 --SELECT @List = COALESCE(@List + ',', '') + CAST(Field_Name AS VARCHAR) FROM  #ActiveField; 
--select @List

	set @sql='select  '+@List+' into #tempp from process_'+@Pid+'_master MasterData 
	left join process_'+@Pid+'_log Calllog on Calllog.Master_Id=MasterData.master_id
	 where '
	 begin
	 set @sql+= 'log_id ='''+@log_id+''''
	 end
	set @sql+='Select * from #tempp'
	--offset '+@pageindex+' rows fetch next '+@pagesize+' row only 
	--select count(*) from #tempp 
	--print(@sql)
	exec  (@sql)
END


GO

/****** Object:  StoredProcedure [dbo].[QA_Performers_Report]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[QA_Performers_Report]
@tlid VARCHAR(30)='', @agentid VARCHAR(30)='', @qaid VARCHAR(30)='', @from VARCHAR(10)='', @to VARCHAR(10)='', @reporttype VARCHAR(30)='',@Process_id VARCHAR(10)='',@UserId VARCHAR(10)=''

AS
BEGIN

	DECLARE @SQL VARCHAR(MAX)
	
	set @SQL='DECLARE @RANK table (SrNo int)
			  INSERT INTO @RANK(SrNo) VALUES (''1''),(''2''),(''3'')
	
			  DECLARE @Performers table (SrNo int,Agent_Name varchar(50),CallScore varchar(5),[Week] varchar(5))
			  INSERT INTO @Performers (SrNo,Agent_Name,CallScore,[Week])
			  select top 3 ROW_NUMBER() OVER (Order By Avg(External_Performance) desc) SrNo,Agent_Name,Avg(External_Performance)[CallScore],
			  case when day(date_of_audit) between 1 and 7 then ''1'' when day(date_of_audit) between 8 and 15 then ''2'' when day(date_of_audit) between 16 and 23 then ''3'' else ''4'' end [Week] 
			  from QA_AgentDetails where 1=1'
			  IF(@tlid!='0')
				SET @SQL+=' AND TLname='''+@tlid+''' ';
			  IF(@agentid!='0')
				SET @SQL+=' AND Agent_Name='''+@agentid+''' ';
			  IF(@qaid!='0')
				SET @SQL+=' AND Auditor_Name='''+@qaid+''' ';
			  
			  SET @SQL+=CASE WHEN @from='' THEN ' AND CONVERT(DATE,Date_of_Audit)>=CONVERT(DATE,Getdate()) ' ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END;
			  
			  SET @SQL+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END;
			 
			  set @SQL=@SQL+' group by Agent_Name ,
			  case when day(date_of_audit) between 1 and 7 then ''1'' when day(date_of_audit) between 8 and 15 then ''2'' when day(date_of_audit) between 16 and 23 then ''3'' else ''4'' end
			  Order by Avg(External_Performance) desc;
			  
			  select r.SrNo,[Week],isnull(Agent_Name,'''')Agent_Name,isnull(CallScore,''0'')CallScore from @RANK r  Left Join @Performers p on r.SrNo=p.SrNo;'
			  
			  
			  set @SQL+='DECLARE @RANK_TL table (SrNo int)
						INSERT INTO @RANK_TL(SrNo) VALUES (''1'')
			  
			  DECLARE @Performers_TL table (SrNo int,TLname varchar(50),CallScore varchar(5),[Week] varchar(5))
			  INSERT INTO @Performers_TL (SrNo,TLname,CallScore,[Week])
			  select  top 1 ROW_NUMBER() OVER (Order By Avg(External_Performance) desc) SrNo,TLname,Avg(External_Performance)[CallScore] ,
			  case when day(date_of_audit) between 1 and 7 then ''1'' when day(date_of_audit) between 8 and 15 then ''2'' when day(date_of_audit) between 16 and 23 then ''3'' else ''4'' end [Week]
			  from QA_AgentDetails where 1=1'
			  IF(@tlid!='0')
				SET @SQL+=' AND TLname='''+@tlid+''' ';
			  IF(@agentid!='0')
				SET @SQL+=' AND Agent_Name='''+@agentid+''' ';
			  IF(@qaid!='0')
				SET @SQL+=' AND Auditor_Name='''+@qaid+''' ';
			  
			  SET @SQL+=CASE WHEN @from='' THEN ' AND CONVERT(DATE,Date_of_Audit)>=CONVERT(DATE,Getdate()) ' ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END;
			  
			  SET @SQL+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END;
			 
			  set @SQL=@SQL+' group by TLname ,
			  case when day(date_of_audit) between 1 and 7 then ''1'' when day(date_of_audit) between 8 and 15 then ''2'' when day(date_of_audit) between 16 and 23 then ''3'' else ''4'' end
			  Order by Avg(External_Performance) desc;
			  
			  select r.SrNo,[Week],isnull(TLname,'''')TLname,isnull(CallScore,''0'')CallScore from @RANK_TL r  Left Join @Performers_TL p on r.SrNo=p.SrNo'

	EXEC(@SQL)
	print(@SQL)
	--exec QA_Performers_Report '0','0','0','20171231','','Performers','1','1'
END
GO

/****** Object:  StoredProcedure [dbo].[QA_Performers_Report_WeekWise]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[QA_Performers_Report_WeekWise]
@tlid VARCHAR(30)='', @agentid VARCHAR(30)='', @qaid VARCHAR(30)='',@month VARCHAR(10), @reporttype VARCHAR(30)='',@Process_id VARCHAR(10)='',@UserId VARCHAR(10)=''

AS
BEGIN

	DECLARE @SQL VARCHAR(MAX)
	
	set @SQL='DECLARE @RANK table (SrNo int)
			  INSERT INTO @RANK(SrNo) VALUES (''1''),(''2''),(''3'')
	
			  DECLARE @Performers table (SrNo int,Agent_Name varchar(50),CallScore varchar(5),[Week] varchar(5))
			  INSERT INTO @Performers (SrNo,Agent_Name,CallScore,[Week])
			  select top 3 ROW_NUMBER() OVER (Order By Avg(External_Performance) desc) SrNo,Agent_Name,Avg(External_Performance)[CallScore],
			  case when day(date_of_audit) between 1 and 7 then ''1'' when day(date_of_audit) between 8 and 15 then ''2'' when day(date_of_audit) between 16 and 23 then ''3'' else ''4'' end [Week] 
			  from QA_AgentDetails where 1=1'
			  IF(@tlid!='0')
				SET @SQL+=' AND TLname='''+@tlid+''' ';
			  IF(@agentid!='0')
				SET @SQL+=' AND Agent_Name='''+@agentid+''' ';
			  IF(@qaid!='0')
				SET @SQL+=' AND Auditor_Name='''+@qaid+''' ';
			  
			  SET @sql+=CASE WHEN @month='' THEN ' and year(Date_of_Audit)= year(getdate()) and month(Date_of_Audit)>=month(getdate())' ELSE 
		'and year(Date_of_Audit)= year(getdate()) and month(Date_of_Audit)='''+@month+''' ' END;

			  set @SQL=@SQL+' group by Agent_Name ,
			  case when day(date_of_audit) between 1 and 7 then ''1'' when day(date_of_audit) between 8 and 15 then ''2'' when day(date_of_audit) between 16 and 23 then ''3'' else ''4'' end
			  Order by Avg(External_Performance) desc;
			  
			  select r.SrNo,[Week],isnull(Agent_Name,'''')Agent_Name,isnull(CallScore,''0'')CallScore from @RANK r  Left Join @Performers p on r.SrNo=p.SrNo;'
			  
			  
			  set @SQL+='DECLARE @RANK_TL table (SrNo int)
						INSERT INTO @RANK_TL(SrNo) VALUES (''1'')
			  
			  DECLARE @Performers_TL table (SrNo int,TLname varchar(50),CallScore varchar(5),[Week] varchar(5))
			  INSERT INTO @Performers_TL (SrNo,TLname,CallScore,[Week])
			  select  top 1 ROW_NUMBER() OVER (Order By Avg(External_Performance) desc) SrNo,TLname,Avg(External_Performance)[CallScore] ,
			  case when day(date_of_audit) between 1 and 7 then ''1'' when day(date_of_audit) between 8 and 15 then ''2'' when day(date_of_audit) between 16 and 23 then ''3'' else ''4'' end [Week]
			  from QA_AgentDetails where 1=1'
			  IF(@tlid!='0')
				SET @SQL+=' AND TLname='''+@tlid+''' ';
			  IF(@agentid!='0')
				SET @SQL+=' AND Agent_Name='''+@agentid+''' ';
			  IF(@qaid!='0')
				SET @SQL+=' AND Auditor_Name='''+@qaid+''' ';
			  
			  SET @sql+=CASE WHEN @month='' THEN ' and year(Date_of_Audit)= year(getdate()) and month(Date_of_Audit)>=month(getdate())' ELSE 
		'and year(Date_of_Audit)= year(getdate()) and month(Date_of_Audit)='''+@month+''' ' END;
			 
			  set @SQL=@SQL+' group by TLname ,
			  case when day(date_of_audit) between 1 and 7 then ''1'' when day(date_of_audit) between 8 and 15 then ''2'' when day(date_of_audit) between 16 and 23 then ''3'' else ''4'' end
			  Order by Avg(External_Performance) desc;
			  
			  select r.SrNo,[Week],isnull(TLname,'''')TLname,isnull(CallScore,''0'')CallScore from @RANK_TL r  Left Join @Performers_TL p on r.SrNo=p.SrNo'

	EXEC(@SQL)
	print(@SQL)
	--exec QA_Performers_Report '0','0','0','20171231','','Performers','1','1'
END

GO

/****** Object:  StoredProcedure [dbo].[QA_Sale_Wise_Report]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--QA_Sale_Wise_Report '0','0','0','2019-12-01','2019-12-21','0','1','0'
CREATE PROC [dbo].[QA_Sale_Wise_Report]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

	DECLARE @SQL VARCHAR(MAX)
	declare @LeadCallStatus varchar(50);
	select @LeadCallStatus=Lead_Disposition from QA_Page_Master1 where process_id=@Process_id;



	set @SQL='select ROW_NUMBER() OVER (Order by pl.Agent_Name)SrNo,pl.Agent_Name,Emp_Code,
	SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 1 and 7 THEN 1 ELSE 0 END) [Week 1],
SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 8 and 15 THEN 1 ELSE 0 END) [Week 2],
SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 15 and 23 THEN 1 ELSE 0 END) [Week 3],
SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time)>= 24 THEN 1 ELSE 0 END) [Week 4],sum(ISNULL(Fatal_Error_Call,0))[Fatal Calls]
			  into #temp1 from Process_'+@Process_id+'_Log pl
			  LEFT JOIN Admin_Agent_Master aam on aam.Agent_Name=pl.Agent_Name
			  left join QA_AgentDetails qa on pl.Log_Id=qa.Log_Id
			  --Left Join User_Master UM on UM.User_Id=pl.Team_Leader_Id
			  where ISNULL(pl.Agent_Name,'''') not in ('''',''admin'') and 1=1'
		IF(@tlid!='0')
		SET @sql+=' AND User_Name='''+@tlid+''' ';
		IF(@agentid!='0')
		SET @sql+=' AND pl.Agent_Name='''+@agentid+''' ';
		IF(@qaid!='0')
		SET @sql+=' AND QA_Assign_Agent='''+@qaid+''' ';
		SET @sql+=CASE WHEN @from='' THEN ' AND  CONVERT(DATE,Start_time)>=CONVERT(DATE,Getdate()) ' ELSE ' AND CONVERT(DATE,Start_time)>='''+@from+''' ' END;
		SET @sql+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Start_time)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Start_time)<='''+@to+''' ' END;
	set @SQL+='group by pl.Agent_Name,Emp_Code;

	select SrNo,Agent_Name,Emp_Code,[Week 1],[Week 2],[Week 3],[Week 4],[Fatal Calls] from #temp1
	UNION ALL
	select NULL,''Total Sale Count'','''',SUM([Week 1]),SUM([Week 2]),SUM([Week 3]),SUM([Week 4]),sum([Fatal Calls]) from #temp1 order by 2'

	set @SQL+='select ROW_NUMBER() OVER (Order by CASE WHEN GROUPING(User_Name)=1 THEN 1 ELSE 0 END)SrNo,
			   CASE WHEN GROUPING(User_Name)=1 THEN ''Grand Total'' ELSE User_Name END [TL_Name],
			    SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 1 and 7 THEN 1 ELSE 0 END) [Week 1],
				SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 8 and 15 THEN 1 ELSE 0 END) [Week 2],
				SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 15 and 23 THEN 1 ELSE 0 END) [Week 3],
				SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time)>= 24 THEN 1 ELSE 0 END) [Week 4],sum(ISNULL(Fatal_Error_Call,0))[Fatal Calls]
				from Process_'+@Process_id+'_Log pl left join User_Master on pl.Team_Leader_Id=User_Id left join QA_AgentDetails qa on pl.Log_Id=qa.Log_Id
				where ISNULL(pl.Agent_Name,'''') not in ('''',''admin'') and 1=1'
				IF(@tlid!='0')
				SET @sql+=' AND User_Name='''+@tlid+''' ';
				IF(@agentid!='0')
				SET @sql+=' AND pl.Agent_Name='''+@agentid+''' ';
				IF(@qaid!='0')
				SET @sql+=' AND QA_Assign_Agent='''+@qaid+''' ';
				SET @sql+=CASE WHEN @from='' THEN ' AND CONVERT(DATE,Start_time)>=CONVERT(DATE,Getdate()) ' ELSE ' AND CONVERT(DATE,Start_time)>='''+@from+''' ' END;
				SET @sql+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Start_time)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Start_time)<='''+@to+''' ' END;
			set @SQL+='group by User_Name with cube'

	EXEC(@SQL)
	PRINT(@SQL)
END
GO

/****** Object:  StoredProcedure [dbo].[QA_Sale_Wise_Report_WeekWise]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




--QA_Sale_Wise_Report '0','0','0','2019-12-01','2019-12-21','0','1','0'
CREATE PROC [dbo].[QA_Sale_Wise_Report_WeekWise]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @month VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

	DECLARE @SQL VARCHAR(MAX)
	declare @LeadCallStatus varchar(50);
	select @LeadCallStatus=Lead_Disposition from QA_Page_Master1 where process_id=@Process_id;



	set @SQL='select ROW_NUMBER() OVER (Order by pl.Agent_Name)SrNo,pl.Agent_Name,Emp_Code,
	SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 1 and 7 THEN 1 ELSE 0 END) [Week 1],
SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 8 and 15 THEN 1 ELSE 0 END) [Week 2],
SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 15 and 23 THEN 1 ELSE 0 END) [Week 3],
SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time)>= 24 THEN 1 ELSE 0 END) [Week 4],sum(ISNULL(Fatal_Error_Call,0))[Fatal Calls]
			  into #temp1 from Process_'+@Process_id+'_Log pl
			  LEFT JOIN Admin_Agent_Master aam on aam.Agent_Name=pl.Agent_Name
			  left join QA_AgentDetails qa on pl.Log_Id=qa.Log_Id
			  --Left Join User_Master UM on UM.User_Id=pl.Team_Leader_Id
			  where ISNULL(pl.Agent_Name,'''') not in ('''',''admin'') and 1=1'
		IF(@tlid!='0')
		SET @sql+=' AND User_Name='''+@tlid+''' ';
		IF(@agentid!='0')
		SET @sql+=' AND pl.Agent_Name='''+@agentid+''' ';
		IF(@qaid!='0')
		SET @sql+=' AND QA_Assign_Agent='''+@qaid+''' ';
		SET @sql+=CASE WHEN @month='' THEN ' and year(Start_time)= year(getdate()) and month(Start_time)>=month(getdate())' ELSE 
		'and year(Start_time)= year(getdate()) and month(Start_time)='''+@month+''' ' END;
	set @SQL+='group by pl.Agent_Name,Emp_Code;

	select SrNo,Agent_Name,Emp_Code,[Week 1],[Week 2],[Week 3],[Week 4],[Fatal Calls] from #temp1
	UNION ALL
	select NULL,''Total Sale Count'','''',SUM([Week 1]),SUM([Week 2]),SUM([Week 3]),SUM([Week 4]),sum([Fatal Calls]) from #temp1 order by 2'

	set @SQL+='select ROW_NUMBER() OVER (Order by CASE WHEN GROUPING(User_Name)=1 THEN 1 ELSE 0 END)SrNo,
			   CASE WHEN GROUPING(User_Name)=1 THEN ''Grand Total'' ELSE User_Name END [TL_Name],
			    SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 1 and 7 THEN 1 ELSE 0 END) [Week 1],
				SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 8 and 15 THEN 1 ELSE 0 END) [Week 2],
				SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time) between 15 and 23 THEN 1 ELSE 0 END) [Week 3],
				SUM(CASE WHEN CallStatus='''+@LeadCallStatus+''' and day(Start_Time)>= 24 THEN 1 ELSE 0 END) [Week 4],sum(ISNULL(Fatal_Error_Call,0))[Fatal Calls]
				from Process_'+@Process_id+'_Log pl left join User_Master on pl.Team_Leader_Id=User_Id left join QA_AgentDetails qa on pl.Log_Id=qa.Log_Id
				where ISNULL(pl.Agent_Name,'''') not in ('''',''admin'') and 1=1'
				IF(@tlid!='0')
				SET @sql+=' AND User_Name='''+@tlid+''' ';
				IF(@agentid!='0')
				SET @sql+=' AND pl.Agent_Name='''+@agentid+''' ';
				IF(@qaid!='0')
				SET @sql+=' AND QA_Assign_Agent='''+@qaid+''' ';
				SET @sql+=CASE WHEN @month='' THEN ' and year(Start_time)= year(getdate()) and month(Start_time)>=month(getdate())' ELSE 
		'and year(Start_time)= year(getdate()) and month(Start_time)='''+@month+''' ' END;
			set @SQL+='group by User_Name with cube'

	EXEC(@SQL)
	PRINT(@SQL)
END

GO

/****** Object:  StoredProcedure [dbo].[QA_Team_Wise_Score_Report]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--exec QA_Team_Wise_Score_Report '0','0','0','','','TeamWiseScore','1','1'
CREATE PROC [dbo].[QA_Team_Wise_Score_Report] @tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10) AS BEGIN DECLARE @sql NVARCHAR(MAX)=''; SET @sql=' SELECT ROW_NUMBER() OVER (Order by TLname)SrNo,TLname TL_Name,AVG(External_Performance) Average_of_Call_Score_per,
case when AVG(External_Performance) >=95 then ''Green'' when AVG(External_Performance) between 90 and 94 then ''Yellow'' else ''Red'' end [Color] from QA_AgentDetails WHERE 1=1  ' 	IF(@tlid!='0') 	SET @sql+=' AND TLname='''+@tlid+''' '; 	IF(@agentid!='0') 	SET @sql+=' AND Agent_Name='''+@agentid+''' '; 	IF(@qaid!='0') 	SET @sql+=' AND Auditor_Name='''+@qaid+''' '; 	SET @sql+=CASE WHEN @from='' THEN ' AND  CONVERT(DATE,Date_of_Audit)>=CONVERT(DATE,Getdate()) ' ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END; 	SET @sql+=CASE WHEN @to='' THEN 'AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END; SET @sql+=' GROUP BY TLname '; 
EXECUTE sp_executesql @SQL 
print (@SQL)
END 

GO

/****** Object:  StoredProcedure [dbo].[QA_Telecaller_WISE_REPORT]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[QA_Telecaller_WISE_REPORT]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

DECLARE @sql NVARCHAR(MAX)='';

SET @sql='select convert(varchar(10),start_time,103)[Date],pl.Agent_Name,sum(case when CallStatus=''Resolved'' then 1 else 0 end)[Total leads generated] ,
sum(case when CallStatus=''Resolved'' and isnull(QA_Disposition,'''')!='''' then 1 else 0 end)[Total leads audited],
sum(case when CallStatus=''Resolved'' and isnull(QA_Disposition,'''')='''' then 1 else 0 end)[Audit pending],
sum(case when CallStatus=''Resolved'' and QA_Disposition in (''Quality Rework'',''Quality Hold'') then 1 else 0 end)[Rework],
sum(ISNULL(Fatal_Error_Call,0))[Fatal Calls],sum(ISNULL(WrongDisposition,0))[Wrong Disp],sum(ISNULL(convert(int,FeedbackDone),0))[Feedback Accept]
from process_'+@Process_id+'_log pl
left join QA_AgentDetails qa on pl.Log_Id=qa.Log_Id
where isnull(pl.Agent_Name,'''')!='''' and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) '
	IF(@tlid!='0')
	SET @sql+=' AND qa.TLname='''+@tlid+'''';
	IF(@agentid!='0')
	SET @sql+=' AND pl.Agent_Name='''+@agentid+''' ';
	IF(@qaid!='0')
	SET @sql+='';
	SET @sql+=CASE WHEN @from='' THEN 'AND CONVERT(DATE,Start_Time) BETWEEN CONVERT(DATE,GETDATE())' ELSE ' AND CONVERT(DATE,Start_Time) BETWEEN '''+@from+''' ' END;
	SET @sql+=CASE WHEN @to='' THEN 'AND CONVERT(DATE,GETDATE())' ELSE ' AND '''+@to+''' ' END;

SET @sql+='group by pl.Agent_Name,convert(varchar(10),start_time,103) Order by convert(varchar(10),start_time,103),pl.Agent_Name';

EXECUTE sp_executesql @SQL
print (@sql)

END

--exec QA_Telecaller_WISE_report '0','0','0','20181230','','AgentWiseDetail','1','1'


GO

/****** Object:  StoredProcedure [dbo].[QA_TL_WISE_REPORT]    Script Date: 5/29/2021 4:08:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO







CREATE PROC [dbo].[QA_TL_WISE_REPORT]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(15), @to VARCHAR(15), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

DECLARE @sql NVARCHAR(MAX)='';

SET @sql='select convert(varchar,Date_of_Audit,103)[Date], TLname,
		  sum(case when Disposition is not null then 1 else 0 end)[Total leads generated] , 
		  sum(case when isnull(Disposition,'''')!='''' then 1 else 0 end)[Total leads audited],
		  sum(case when Disposition in(''Quality Rework Quality Reject'') then 1 else 0 end)[Audit pending],
		  sum(case when Disposition=''Quality Approve'' then 1 else 0 end)[Audit ok],
		  sum(case when Disposition in (''Quality Rework'',''Quality Hold'') then 1 else 0 end)[Rework],
		  sum(case when Disposition=''Quality Reject'' then 1 else 0 end)[Reject]
		  from QA_AgentDetails
		  where P_id='''+@Process_id+''' and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) '
	IF(@tlid!='0')
	SET @sql+=' AND TLname='''+@tlid+''' ';
	IF(@agentid!='0')
	SET @sql+=' AND Agent_Name='''+@agentid+''' ';
	IF(@qaid!='0')
	SET @sql+=' AND Auditor_Name='''+@qaid+''' ';
	
	SET @sql+=CASE WHEN @from='' THEN ' AND  CONVERT(DATE,Date_of_Audit)>=CONVERT(DATE,GETDATE()+1) ' ELSE ' AND CONVERT(DATE,Date_of_Audit)>='''+@from+''' ' END;
	SET @sql+=CASE WHEN @to='' THEN ' AND CONVERT(DATE,Date_of_Audit)<=CONVERT(DATE,GETDATE()+1)' ELSE ' AND CONVERT(DATE,Date_of_Audit)<='''+@to+''' ' END;

SET @sql+='group by TLname,convert(varchar,Date_of_Audit,103) order by convert(varchar,Date_of_Audit,103),TLname';

EXECUTE sp_executesql @SQL
print(@sql)
END

--QA_TL_WISE_REPORT 'raj','bharat','0','2019-11-05','','0'


GO


